﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace IBS
{
    public partial class IbsDataContext
    {
        public IEnumerable<Site> GetSites(DateTime lastSyncDate)
        {
            var sql = @"select GUID_Record, siteibscode, sitename, rccode,Address, dateupdate  from tbsite
                        where 1 = 1                            
                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                            AND COMPANY	= 2	
                            AND TYPE_ID = 1
                        order by dateupdate";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<Site>(sql);
        }

        public IEnumerable<SaleOrderMsg> GetSaleOrderMsg(Int64 headerId)
        {
            var sql = @"SELECT S.ID_RECORD,
                           DOC_NUMBER,
                           DOC_DATE,
                           COMPANY_KEY,
                           SUP.SUPNAME,
                           LOCATION_KEY,
                           SITE.RCCODE,
                           SITE.SITENAME,
                           ITEM_KEY,
                           PRICE,
                           ACTUAL_PRICE,
                           (select sum (PRICE*QUANTITY) from EDEX.WH_SALESORDERLINES lines where lines.ID_INVOICE =  {0}) TOTAL,
                           (select sum (ACTUAL_PRICE*QUANTITY) from EDEX.WH_SALESORDERLINES lines where lines.ID_INVOICE =  {0}) TOTAL_ACTUAL
                      FROM EDEX.WH_SALESORDER S,
                           EDEX.WH_SALESORDERLINES SL,
                           ibsdbo.TBSITE SITE,
                           IBSDBO.TBSUPPLIER SUP
                     WHERE     S.ID_RECORD = SL.ID_INVOICE
                           AND s.LOCATION_KEY = SITE.SITEIBSCODE
                           AND S.COMPANY_KEY = SUP.SUPIBSCODE
                           AND SL.PRICE <> SL.ACTUAL_PRICE
                    AND S.ID_RECORD = {0}
                    ORDER BY SL.ORDER_NUM";

            sql = String.Format(sql, headerId);

            return new DB
                .Oracle(this.connectionString)
                .Excecute<SaleOrderMsg>(sql);
        }


        public IEnumerable<Manager> GetManagerInfo(Int64 COMPANY_KEY)
        {
            var sql = @"SELECT MAN.EMAIL
                        FROM IBSDBO.TBCATMANAGER MAN, IBSDBO.TBCOMPMANAGER CM
                        WHERE     MAN.MANAGER_ID = CM.ID_MANAGER
                        AND CM.SUPIBSCODE = {0}";
            sql = String.Format(sql, COMPANY_KEY.ToString());

            return new DB
               .Oracle(this.connectionString)
               .Excecute<Manager>(sql);
        }



        public IEnumerable<Supplier> GetSuppliers(DateTime lastSyncDate)
        {
            var sql = @"select 
                            SUPIBSCODE, SUPNAME, CHDATE from tbsupplier
                        where 1 = 1          
                            AND  SUPPLIER_TYPE = 1	  
                             AND SUPIBSCODE >= 1000             
                            AND SUPIBSCODE < 5000
                            AND CHDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                        order by CHDATE";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<Supplier>(sql);
        }


        public IEnumerable<Item> GetItems(DateTime lastSyncDate)
        {
            var sql = @"select 
                            ITEMCODE, SUPIBSCODE, ITEMSTATCODE, LONGNAME, SHORTNAME, CHDATE 
                        from 
                            TBITEM 
                        where 1 = 1
                            AND ITEMSTATCODE IN (1, 2)
                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                             AND SUPIBSCODE >= 1000             
                            AND SUPIBSCODE < 5000
                        order by CHDATE";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<Item>(sql);
        }

        public DateTime GetLastSyncDate(string paramName)
        {
            using (var connection = new OracleConnection(this.connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    var sql = @"select Value from edex.TBSETTING where Name = '{0}'";
                    sql = String.Format(sql, paramName);
                    command.CommandText = sql;
                    using (var reader = command.ExecuteReader())
                    {
                        reader.Read();
                        return DateTime.Parse(reader.GetString(0));
                    }
                }
            }
        }

        public void SetLastSyncDate(String LastSyncDate, string paramName)
        {
            using (var connection = new OracleConnection(this.connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    var sql = @"update edex.TBSETTING set value = '{0}' where name = '{1}'";
                    sql = String.Format(sql, LastSyncDate, paramName);
                    command.CommandText = sql;
                    command.ExecuteReader();
                }
            }
        }

    }
}
